#!/usr/bin/perl -w

#########################################################################################################
# Options - Optional
#########################################################################################################
my $numdays = shift;

#########################################################################################################
# Modules
#########################################################################################################
use XML::Simple;
use Data::Dumper;
use DBI;
use LWP::UserAgent;

#########################################################################################################
# Program Variables
#########################################################################################################
use FindBin qw($Bin);
use lib "$Bin";
use config;

#########################################################################################################
#########################################################################################################
my $xmlcontent;

# Open the Database
my $dbh = DBI->connect("dbi:SQLite:dbname=$database_file", "", "");

if (&read_config_option("controller_type") ne "Apex") {

    # Not running an Apex controller, or it's not configured, abort
    exit 100;
}

my $controller_url = &read_config_option("controller_url") . "/cgi-bin/datalog.xml";

if (defined($numdays)) {
    $prevdate       = `date "+%y%m%d" --date="-$numdays days"`;
    $controller_url = &read_config_option("controller_url") . "/cgi-bin/datalog.xml?sdate=$prevdate&days=$numdays";
}

# Program Start
my $ua = LWP::UserAgent->new;
$ua->agent("MyApp/0.1 ");
$ua->timeout("3000");

# Create a request
my $req = HTTP::Request->new(GET => $controller_url);

# Pass request to the user agent and get a response back
my $res = $ua->request($req);

# Check the outcome of the response
if ($res->is_success) {
    $xmlcontent = $res->content;
} else {
    print $res->status_line, "\n";
    exit 100;
}

my $xml  = new XML::Simple;
my $data = $xml->XMLin($xmlcontent);
my %hash = %{$data};

# Close all connections and get rid of temp files
undef $req;
undef $res;

print "Apex data retrieved.\n";

my @recordlist = @{ $hash{record} };

# SQL Statements
my $check_sql  = "SELECT * FROM aquarium WHERE collected=datetime(?) AND name=?";
my $insert_sql = "INSERT INTO aquarium (collected, name, value) VALUES (datetime(?), ?, ?)";

# Prepare SQL statements
my $check_sth  = $dbh->prepare_cached($check_sql);
my $insert_sth = $dbh->prepare_cached($insert_sql);

foreach $record (@recordlist) {
    my %recordhash  = %{$record};
    my $record_date = $recordhash{date};

    my $sqldate = "Invalid";

    # 03/01/2012 00:10:00
    if ($record_date =~ /(\d\d)\/(\d\d)\/(\d\d\d\d) (\d\d:\d\d:\d\d)/) {
        my $month = $1;
        my $day   = $2;
        my $year  = $3;
        my $time  = $4;

        $sqldate = "$year-$month-$day $time";
    }

    my $probe_sth = $dbh->prepare("SELECT * FROM controller");
    $probe_sth->execute();
    while (my $proberef = $probe_sth->fetchrow_hashref()) {
        my %probehash = %{$proberef};
        my $probe     = $probehash{probe};
        my $test      = $probehash{test};

        print "Checking $sqldate ($probe) ... ";

        $check_sth->execute($sqldate, $test);
        my $check_ref = $check_sth->fetchrow_hashref();
        if (!defined($check_ref)) {
            my $recvalue = $recordhash{probe}{$probe}{value};
            if (defined($recvalue)) {
                $insert_sth->execute($sqldate, $test, $recvalue);
                print "$test added to db. (recname = $probe, recvalue=$recvalue)\n";
            } else {
                print "$test not added, no data found in Apex for $test at $sqldate\n";
            }
        } else {
            print "$test already in db.\n";
        }
    }
}

# Close up the statement handlers
$check_sth->finish;
$insert_sth->finish;

# Close the database
$dbh->disconnect;

exit 0;

